#!/bin/bash
APP=online_edu

if [ -n "$2" ] ;then
    do_date=$2
else 
    do_date=`date -d "-1 day" +%F`
fi


dwd_trade_cart_add_inc="
insert overwrite table ${APP}.dwd_trade_cart_add_inc partition (dt = '$do_date')
select
       id,
       user_id,
       t1.course_id,
       course_name,
       category_id,
       category_name,
       subject_id,
       subject_name,
       cart_price,
       session_id,
       create_time,
       date_format(create_time, 'yyyy-MM-dd') data_id
from (
         select data.id,
                data.user_id,
                data.course_id,
                data.cart_price,
                data.session_id,
                data.create_time
         from ${APP}.ods_cart_info_inc
         where dt = '$do_date'
           and type = 'bootstrap-insert'
     ) t1
         left join
     (
         select course_id,
                course_name,
                category_id,
                category_name,
                subject_id,
                subject_name
         from ${APP}.dim_course_subject_category_full
         where dt = '$do_date'
     ) t2
     on t1.course_id = t2.course_id;
"

dwd_trade_order_detail_inc=" 
insert overwrite table ${APP}.dwd_trade_order_detail_inc partition (dt = '$do_date')
select id,
       t1.course_id,
       course_name,
       category_id,
       category_name,
       subject_id,
       subject_name,
       t1.order_id,
       user_id,
       origin_amount,
       coupon_reduce,
       final_amount,
       create_time,
       date_format(create_time, 'yyyy-MM-dd') data_id,
       t4.province_id,
       province_name,
       area_code,
        iso_code,
        iso_3166_2
from (
         select data.id id,
                data.course_id course_id,
                data.order_id order_id,
                data.user_id user_id,
                data.origin_amount origin_amount,
                data.coupon_reduce coupon_reduce,
                data.final_amount final_amount,
                data.create_time create_time
         from ${APP}.ods_order_detail_inc
         where dt = '$do_date'
           and type = 'bootstrap-insert'
     ) t1
         left join
     (
         select course_id,
                course_name,
                category_id,
                category_name,
                subject_id,
                subject_name
         from ${APP}.dim_course_subject_category_full
         where dt = '$do_date'
     ) t2
on t1.course_id = t2.course_id
left join
    (
        select data.id order_id,
               data.province_id province_id
        from ${APP}.ods_order_info_inc
        where dt = '$do_date'
        )t3
on t1.order_id = t3.order_id
left join
    (
        select
                id province_id,
                name province_name,
                area_code,
                iso_code,
                iso_3166_2
        from ${APP}.ods_base_province_full
        where dt = '$do_date'
        )t4
on t3.province_id = t4.province_id;
"

dwd_trade_pay_detail_suc_inc="
insert overwrite table ${APP}.dwd_trade_pay_detail_suc_inc partition (dt = '$do_date')
select id,
       out_trade_no,
       t1.order_id,
       user_id,
       alipay_trade_no,
       total_amount,
       trade_body,
       payment_type,
       payment_status,
       create_time,
       callback_time,
       date_format(callback_time, 'yyyy-MM-dd') data_id
from (
         select data.id,
                data.out_trade_no,
                data.order_id,
                data.alipay_trade_no,
                data.total_amount,
                data.trade_body,
                data.payment_type,
                data.payment_status,
                data.create_time,
                data.callback_time
         from ${APP}.ods_payment_info_inc
         where dt = '$do_date'
           and type = 'bootstrap-insert'
     ) t1
         left join
     (
         select data.id as order_id,
                data.user_id
         from ${APP}.ods_order_info_inc
         where dt = '$do_date'
           and type = 'bootstrap-insert'
     ) t2
on t1.order_id = t2.order_id;
"

dwd_traffic_page_view_inc="
insert overwrite table ${APP}.dwd_traffic_page_view_inc partition (dt='$do_date')
select
    province_id,
    brand,
    channel,
    is_new,
    model,
    mid_id,
    operate_system,
    user_id,
    version_code,
    page_item,
    page_item_type,
    last_page_id,
    page_id,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd') date_id,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd HH:mm:ss') view_time,
    concat(mid_id,'-',last_value(session_start_point,true) over (partition by mid_id order by ts)) session_id,
    during_time
from
(
    select
        common.ar province_id,
        common.ba brand,
        common.ch channel,
        common.is_new is_new,
        common.md model,
        common.mid mid_id,
        common.os operate_system,
        common.uid user_id,
        common.vc version_code,
        page.during_time during_time,
        page.item page_item,
        page.item_type page_item_type,
        page.last_page_id last_page_id,
        page.page_id page_id,
        ts,
        if(page.last_page_id is null,ts,null) session_start_point
    from ${APP}.ods_log_inc
    where dt='$do_date'
    and page is not null
)log;
"
dwd_traffic_video_play_inc="
insert overwrite table ${APP}.dwd_traffic_video_play_inc partition (dt='$do_date')
select
       user_id,
       course_id,
       chapter_id,
       video_id,
       play_sec,
       position_sec,
       date_id
       from
(select
    data.user_id      user_id,
    data.course_id    course_id,
    data.chapter_id   chapter_id
from ${APP}.ods_user_chapter_process_inc
where type = 'bootstrap-insert'
and dt = '$do_date') oucpi
join
(
select
        common.uid uid,
        appvideo.play_sec play_sec,
        appvideo.position_sec position_sec,
        appVideo.video_id video_id,
        date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd') date_id
    from ${APP}.ods_log_inc
    where dt = '$do_date'
        and appVideo.play_sec is not null
) log
on log.uid = oucpi.user_id;
"



dwd_user_register_inc="
insert overwrite table ${APP}.dwd_user_register_inc partition(dt='$do_date')
select
    ui.user_id,
    date_format(create_time,'yyyy-MM-dd') date_id,
    create_time,
    channel,
    province_id,
    version_code,
    mid_id,
    brand,
    model,
    operate_system
from
(
    select
        data.id user_id,
        data.create_time
    from ${APP}.ods_user_info_inc
    where dt='$do_date'
    and type='insert'
)ui
left join
(
    select
        common.ar area_code,
        common.ba brand,
        common.ch channel,
        common.md model,
        common.mid mid_id,
        common.os operate_system,
        common.uid user_id,
        common.vc version_code
    from ${APP}.ods_log_inc
    where dt='$do_date'
    and page.page_id='register'
    and common.uid is not null
)log
on ui.user_id=log.user_id
left join
(
    select
        concat(id,'0000') province_id,
        area_code
    from ${APP}.ods_base_province_full
    where dt='$do_date'
)bp
on log.area_code=bp.area_code;
"

dwd_user_login_inc="
insert overwrite table ${APP}.dwd_user_login_inc partition(dt='$do_date') 
select
 user_id,
 date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd') date_id,
 date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd HH:mm:ss') login_time,
 area_code,
 channel,
 brand,
 model,
 mid_id,
 operate_system,
 source,
 session_id,
 version_code
from(
 select
  common.uid user_id,
  ts,
  common.ar area_code,
  common.ch channel,
  common.ba brand,
  common.md model,
  common.mid mid_id,
  common.os operate_system,
   common.sc source,
  common.sid session_id,
  common.vc version_code,  
  row_number() over(partition by common.sid order by ts) rn
 from ${APP}.ods_log_inc
 where dt = '$do_date' and page is not null and common.uid is not null
)t1 
where rn =1;
"



dwd_interaction_favor_add_inc="
insert overwrite table ${APP}.dwd_interaction_favor_add_inc partition(dt = '$do_date')
select
 data.id,
 data.user_id,
 data.course_id,
 date_format(data.create_time,'yyyy-MM-dd') date_id,
 data.create_time,
 data.deleted
from ${APP}.ods_favor_info_inc
where dt = '$do_date' and type = 'insert';
"

dwd_behavior_user_chapter_process_inc="
insert overwrite table ${APP}.dwd_behavior_user_chapter_process_inc partition (dt='$do_date')
select
       ucp.id,
       ucp.chapter_id,
       ci.chapter_name,
       ucp.course_id,
       cs.course_name,
       cs.course_slogan,
       cs.course_cover_url,
       ucp.position_sec,
       ci.publisher_id,
       ci.is_free,
       ucp.create_time,
       ucp.update_time,
       ucp.deleted

from
(
    select
        data.id,
        data.course_id,
        data.chapter_id,
        data.position_sec,
        data.create_time,
        data.update_time,
        data.deleted
    from ${APP}.ods_user_chapter_process_inc
    where dt = '$do_date'
    and type = 'insert'
    )ucp
left join
(
    select
        id,
       course_name,
       course_slogan,
       course_cover_url,
       dt
    from ${APP}.ods_course_info_full
    where dt = '$do_date'
   )cs
on ucp.course_id = cs.id
left join
(
     select
        course_id,
        chapter_name,
        publisher_id,
        is_free
    from ${APP}.ods_chapter_info_full
    where dt = '$do_date'
    )ci
on ci.course_id = cs.id;
"


dwd_behavior_user_test_exam_inc="
insert overwrite table ${APP}.dwd_behavior_user_test_exam_inc partition (dt='$do_date')
select
     user_id,
     id,
     paper_title,
     course_id,
     question_id,
     score,
     duration_sec,
     create_time,
     update_time,
     publisher_id,
     deleted
from
(
    select
        id,
        paper_title,
        course_id,
        create_time,
        update_time,
        publisher_id,
        deleted
    from ${APP}.ods_test_paper_full
    where dt = '$do_date'
    ) tp
left join
(
    select
        paper_id,
        question_id,
        score
    from ${APP}.ods_test_paper_question_full
    where dt = '$do_date'
    ) tpq
on tp.id = tpq.paper_id
left join
(
    select
    data.user_id,
    data.paper_id,
    data.duration_sec
    from ${APP}.ods_test_exam_inc
    )e
on tpq.paper_id = e.paper_id;
"



dwd_behavior_user_test_exam_question_inc="
insert overwrite table ${APP}.dwd_behavior_user_test_exam_question_inc partition (dt = '$do_date')
select
    q.paper_id,
    p.paper_title,
    i.question_type,
    i.question_txt,
    i.id,
    q.is_correct
from
(
    select
        data.paper_id,
        data.question_id,
        data.is_correct
    from ${APP}.ods_test_exam_question_inc
    where dt = '$do_date'
    and type = 'bootstrap-insert'
    )q
left join
(
    select
         id,
        course_id,
        paper_title
        from ${APP}.ods_test_paper_full
    where dt = '$do_date'
) p
on q.paper_id = p.id
left join
(
    select
         id,
        question_txt,
        question_type
    from ${APP}.ods_test_question_info_full
    where dt = '$do_date'
) i
on q.question_id = i.id;
"

dwd_behavior_user_chapter_comment_inc="
insert overwrite table ${APP}.dwd_behavior_user_chapter_comment_inc partition(dt='$do_date')
select
    data.id,
    data.user_id,
    data.chapter_id,
    data.course_id,
    date_format(data.create_time,'yyyy-MM-dd'),
    data.comment_txt,
    data.create_time,
    data.deleted
from ${APP}.ods_comment_info_inc
where dt ='$do_date'
and type ='insert';
"

dwd_behavior_user_course_review_inc="
insert overwrite table ${APP}.dwd_behavior_user_course_review_inc partition(dt='$do_date')
select
      data.id,
      data.user_id,
      data.course_id,
      date_format(data.create_time,'yyyy-MM-dd'),
      data.review_txt,
      data.review_stars,
      data.create_time,
      data.deleted
from ${APP}.ods_review_info_inc
where dt ='$do_date'
and type ='insert';
"
case $1 in
    "dwd_trade_cart_add_inc" )
   hive -e "$dwd_trade_cart_add_inc" 
   ;;
"dwd_trade_order_detail_inc" )
   hive -e "$dwd_trade_order_detail_inc" 
   ;;
"dwd_traffic_page_view_inc" )
   hive -e "$dwd_traffic_page_view_inc" 
   ;;
"dwd_trade_pay_detail_suc_inc" )
   hive -e "$dwd_trade_pay_detail_suc_inc" 
   ;;
"dwd_traffic_video_play_inc" )
   hive -e "$dwd_traffic_video_play_inc" 
   ;;
"dwd_user_register_inc" )
   hive -e "$dwd_user_register_inc" 
   ;;
"dwd_user_login_inc" )
   hive -e "$dwd_user_login_inc" 
   ;;
"dwd_interaction_favor_add_inc" )
   hive -e "$dwd_interaction_favor_add_inc" 
   ;;
"dwd_behavior_user_chapter_process_inc" )
   hive -e "$dwd_behavior_user_chapter_process_inc" 
   ;;
"dwd_behavior_user_test_exam_inc" )
   hive -e "$dwd_behavior_user_test_exam_inc" 
   ;;
"dwd_behavior_user_test_exam_question_inc" )
   hive -e "$dwd_behavior_user_test_exam_question_inc" 
   ;;
"dwd_behavior_user_chapter_comment_inc" )
   hive -e "$dwd_behavior_user_chapter_comment_inc" 
   ;;
"dwd_behavior_user_course_review_inc" )
   hive -e "$dwd_behavior_user_course_review_inc" 
   ;;
    "all" )
        hive -e "$dwd_trade_cart_add_inc$dwd_trade_order_detail_inc$dwd_traffic_page_view_inc$dwd_trade_pay_detail_suc_inc$dwd_traffic_video_play_inc$dwd_user_register_inc$dwd_user_login_inc$dwd_interaction_favor_add_inc$dwd_behavior_user_chapter_process_inc$dwd_behavior_user_test_exam_inc$dwd_behavior_user_test_exam_question_inc$dwd_behavior_user_chapter_comment_inc$dwd_behavior_user_course_review_inc"
;;
esac


